import logging
import pymysql
import time
from contextlib import contextmanager
from DBUtils.PooledDB import PooledDB
from pymysql.cursors import Cursor, DictCursor, SSCursor, SSDictCursor


class CursorWithLog(Cursor):
    def execute(self, query, args=None):
        """Execute a query
                        带日志输出的SQL执行。改造了Cursor类，自动输出日志，方便Debug和查问题。
                        :param str query: Query to execute.

                        :param args: parameters used with query. (optional)
                        :type args: tuple, list or dict

                        :return: Number of affected rows
                        :rtype: int

                        If args is a list or tuple, %s can be used as a placeholder in the query.
                        If args is a dict, %(name)s can be used as a placeholder in the query.
                        """
        start_time = time.time()
        try:
            result = super().execute(query, args)
        except Exception as e:
            raise Exception(f"【 {time.asctime()} 】\nFail to execute SQL: 【 {query} 】 \nDATA:  【 {args} 】")
        end_time = time.time()
        cost_time = (end_time - start_time) * 1000
        msg = f"【 {time.asctime()} 】\nSuccess to execute SQL with {cost_time} ms \nSQL: 【 {query} 】 \nDATA:  【 {args} 】"
        logging.warning(msg)
        return result


class DictCursorWithLog(DictCursor, CursorWithLog):
    # 结果返回字典
    pass


class SSCursorWithLog(SSCursor, CursorWithLog):
    pass


class SSDictCursorWithLog(SSDictCursor, CursorWithLog):
    pass


class MySQLDB():
    """
        提供mysql单连，单创建
            Establish a connection to the MySQL database. Accepts several
        arguments:

        :param host: Host where the database server is located
        :param user: Username to log in as
        :param password: Password to use.
        :param database: Database to use, None to not use a particular one.
        :param port: MySQL port to use, default is usually OK. (default: 3306)
        :param bind_address: When the client has multiple network interfaces, specify
            the interface from which to connect to the host. Argument can be
            a hostname or an IP address.
        :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
        :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)
        :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)
        :param charset: Charset you want to use.
        :param sql_mode: Default SQL_MODE to use.
        :param read_default_file:
            Specifies  my.cnf file to read these parameters from under the [client] section.
        :param conv:
            Conversion dictionary to use instead of the default one.
            This is used to provide custom marshalling and unmarshalling of types.
            See converters.
        :param use_unicode:
            Whether or not to default to unicode strings.
            This option defaults to true.
        :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
        :param cursorclass: Custom cursor class to use.
        :param init_command: Initial SQL statement to run when connection is established.
        :param connect_timeout: Timeout before throwing an exception when connecting.
            (default: 10, min: 1, max: 31536000)
        :param ssl:
            A dict of arguments similar to mysql_ssl_set()'s parameters.
        :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate
        :param ssl_cert: Path to the file that contains a PEM-formatted client certificate
        :param ssl_disabled: A boolean value that disables usage of TLS
        :param ssl_key: Path to the file that contains a PEM-formatted private key for the client certificate
        :param ssl_verify_cert: Set to true to check the validity of server certificates
        :param ssl_verify_identity: Set to true to check the server's identity
        :param read_default_group: Group to read from in the configuration file.
        :param autocommit: Autocommit mode. None means use server default. (default: False)
        :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
        :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
            Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
        :param defer_connect: Don't explicitly connect on construction - wait for connect call.
            (default: False)
        :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.
            The class will take the Connection object as the argument to the constructor.
            The class needs an authenticate method taking an authentication packet as
            an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used
            (if no authenticate method) for returning a string from the user. (experimental)
        :param server_public_key: SHA256 authentication plugin public key value. (default: None)
        :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)
        :param compress: Not supported
        :param named_pipe: Not supported
        :param db: **DEPRECATED** Alias for database.
        :param passwd: **DEPRECATED** Alias for password.

        See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the
        specification.

    """
    conf_mysql = {}

    def __init__(self, **conf_mysql):
        if conf_mysql:
            self.mysqlConfig = conf_mysql
        else:
            self.mysqlConfig = self.conf_mysql

        self.__connection__: pymysql.Connection = None

    @property
    def connection(self):
        self.__connection__ = pymysql.connect(**self.mysqlConfig)
        return self.__connection__

    def __exit__(self, exc_type, exc_val, exc_tb):
        try:
            self.__connection__.close()
        except Exception as e:
            pass


class MysqlPool(MySQLDB):
    """
    提供mysql数据库连接池
    Set up the DB-API 2 connection pool.

        creator: either an arbitrary function returning new DB-API 2
            connection objects or a DB-API 2 compliant database module
        mincached: initial number of idle connections in the pool
            (0 means no connections are made at startup)
        maxcached: maximum number of idle connections in the pool
            (0 or None means unlimited pool size)
        maxshared: maximum number of shared connections
            (0 or None means all connections are dedicated)
            When this maximum number is reached, connections are
            shared if they have been requested as shareable.
        maxconnections: maximum number of connections generally allowed
            (0 or None means an arbitrary number of connections)
        blocking: determines behavior when exceeding the maximum
            (if this is set to true, block and wait until the number of
            connections decreases, otherwise an error will be reported)
        maxusage: maximum number of reuses of a single connection
            (0 or None means unlimited reuse)
            When this maximum usage number of the connection is reached,
            the connection is automatically reset (closed and reopened).
        setsession: optional list of SQL commands that may serve to prepare
            the session, e.g. ["set datestyle to ...", "set time zone ..."]
        reset: how connections should be reset when returned to the pool
            (False or None to rollback transcations started with begin(),
            True to always issue a rollback for safety's sake)
        failures: an optional exception class or a tuple of exception classes
            for which the connection failover mechanism shall be applied,
            if the default (OperationalError, InternalError) is not adequate
        ping: determines when the connection should be checked with ping()
            (0 = None = never, 1 = default = whenever fetched from the pool,
            2 = when a cursor is created, 4 = when a query is executed,
            7 = always, and all other bit combinations of these values)

        Establish a connection to the MySQL database. Accepts several
        arguments:

        :param host: Host where the database server is located
        :param user: Username to log in as
        :param password: Password to use.
        :param database: Database to use, None to not use a particular one.
        :param port: MySQL port to use, default is usually OK. (default: 3306)
        :param bind_address: When the client has multiple network interfaces, specify
            the interface from which to connect to the host. Argument can be
            a hostname or an IP address.
        :param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
        :param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)
        :param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)
        :param charset: Charset you want to use.
        :param sql_mode: Default SQL_MODE to use.
        :param read_default_file:
            Specifies  my.cnf file to read these parameters from under the [client] section.
        :param conv:
            Conversion dictionary to use instead of the default one.
            This is used to provide custom marshalling and unmarshalling of types.
            See converters.
        :param use_unicode:
            Whether or not to default to unicode strings.
            This option defaults to true.
        :param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
        :param cursorclass: Custom cursor class to use.
        :param init_command: Initial SQL statement to run when connection is established.
        :param connect_timeout: Timeout before throwing an exception when connecting.
            (default: 10, min: 1, max: 31536000)
        :param ssl:
            A dict of arguments similar to mysql_ssl_set()'s parameters.
        :param ssl_ca: Path to the file that contains a PEM-formatted CA certificate
        :param ssl_cert: Path to the file that contains a PEM-formatted client certificate
        :param ssl_disabled: A boolean value that disables usage of TLS
        :param ssl_key: Path to the file that contains a PEM-formatted private key for the client certificate
        :param ssl_verify_cert: Set to true to check the validity of server certificates
        :param ssl_verify_identity: Set to true to check the server's identity
        :param read_default_group: Group to read from in the configuration file.
        :param autocommit: Autocommit mode. None means use server default. (default: False)
        :param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
        :param max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
            Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
        :param defer_connect: Don't explicitly connect on construction - wait for connect call.
            (default: False)
        :param auth_plugin_map: A dict of plugin names to a class that processes that plugin.
            The class will take the Connection object as the argument to the constructor.
            The class needs an authenticate method taking an authentication packet as
            an argument.  For the dialog plugin, a prompt(echo, prompt) method can be used
            (if no authenticate method) for returning a string from the user. (experimental)
        :param server_public_key: SHA256 authentication plugin public key value. (default: None)
        :param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)
        :param compress: Not supported
        :param named_pipe: Not supported
        :param db: **DEPRECATED** Alias for database.
        :param passwd: **DEPRECATED** Alias for password.

        See `Connection <https://www.python.org/dev/peps/pep-0249/#connection-objects>`_ in the
        specification.

    """
    conf_mysql = {}

    def __init__(self, **conf_mysql):
        if conf_mysql:
            super(MysqlPool, self).__init__(**conf_mysql)
        else:
            super(MysqlPool, self).__init__(**self.conf_mysql)
        self.__pool__: PooledDB = None

    def pool(self):
        self.__pool__ = PooledDB(**self.mysqlConfig)
        return self.__pool__

    @property
    def connection(self):
        self.pool()
        self.__connection__ = self.__pool__.connection()
        return self.__connection__

    def __exit__(self, exc_type, exc_val, exc_tb):
        try:
            self.__connection__.close()
        except Exception as e:
            pass
        try:
            self.__pool__.close()
            pass
        except Exception as e:
            pass


@contextmanager
def mysqlSession(connection):
    MysqlException = False
    connection.ping(reconnect=True)
    cursor = connection.cursor()
    try:
        yield cursor
        connection.commit()
    except Exception as e:
        MysqlException = e
        connection.rollback()
    finally:
        cursor.close()
    if MysqlException:
        raise Exception(MysqlException)


def test_MysqlPool():
    connection = MysqlPool(creator=pymysql, mincached=5, host="127.0.0.1", user='root',
                           passwd='123456', port=3306, charset="utf8", maxshared=0).connection
    with mysqlSession(connection) as sess:
        SQL = "show databases"
        sess.execute(SQL)
        res = sess.fetchall()
        print(res)
    print()


def test_MysqlDB():
    connection = MySQLDB(host="127.0.0.1", user='root',
                         passwd='123456', port=3306, charset="utf8",
                         db="information_schema",
                         cursorclass=SSDictCursorWithLog).connection
    with mysqlSession(connection) as sess:
        # SQL = 'SELECT * FROM COLLATIONS AS a WHERE (a.COLLATION_NAME = %s)'
        SQL = 'SELECT * FROM COLLATIONS AS a WHERE (a.COLLATION_NAME = %s)'
        data = ('big5_chinese_ci',)
        sess.execute(SQL, data)
        res = sess.fetchall()
        print(res)
    print("===========")


if __name__ == '__main__':
    # test_MysqlPool()
    test_MysqlDB()
    # input()
